package On_class.L0420.book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 使用JDBC完成
 */
public class BooksDaoDB implements BooksDao {
    Connection conn=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    @Override
    public void insert(Books books) {
        /* 先调用方法判断，数据库中是否已经存在该id，
         *  若返回null，表示不存在，添加到表中
         *  否则，表示存在，提示错误信息*/
        Books b = select(books.getId());
        if (b == null) {
            /* 利用JDBC加到表中:
             *  1) 利用JDBCUtils 获取数据库连接
             *  2）定义sql语句
             *  3）获取执行sql语句的对象pstmt
             *  4) 执行sql语句
             *  5）利用JDBCUtils 释放资源*/
            try {
                conn = JDBCUtils.getConnection();
                String sql = "insert into books values(?,?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                /* 给占位符？赋值*/
                pstmt.setInt(1, books.getId());
                pstmt.setString(2, books.getName());
                pstmt.setDouble(3, books.getPrice());
                pstmt.setInt(4, books.getNumber());
                pstmt.setString(5, books.getPress());
                /* 执行sql语句*/
                int i = pstmt.executeUpdate();
                if (i > 0) {
                    System.out.println("保存成功");
                } else {
                    System.out.println("保存失败,，请重新添加");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(pstmt, conn);
            }
        } else {
            System.out.println("保存失败，id重复");
        }
    }

    @Override
    public void selectAll() {
        try {
            conn = JDBCUtils.getConnection();
            String sql="select * from books";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            System.out.println("所有图书信息如下：");
            while (rs.next()){
                /* 可以直接输出结果集中的信息
                *  若是要返回值的话，需要先取出结果集中每条记录，
                *    封装成Books对象，再添加到集合返回，如ArrayList<Books>*/
                System.out.println(rs.getInt("id")+"\t"+rs.getString("name")
                +"\t"+rs.getDouble("price")+"\t"+rs.getInt("number")
                +"\t"+rs.getString("press"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
    }

    @Override
    public Books select(int id) {
        try {
            conn = JDBCUtils.getConnection();
            /* 这里的id是数据库表books中的字段id*/
            String sql="select * from books where id=?";
            pstmt=conn.prepareStatement(sql);
            /* 第1个问号赋值为：传入的参数id*/
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            /* 读取结果集rs中的数据，封装为Books对象并返回
            *  此结果集只有1条记录 用if语句即可，若多条用while循环读取*/
            if(rs.next()){
                int ids = rs.getInt("id");
                String name = rs.getString("name");
                double price = rs.getDouble("price");
                int number = rs.getInt("number");
                String press = rs.getString("press");
                Books books=new Books(ids,name,price,number,press);
                return books;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return null;
    }

    @Override
    public void update(int id, Books b) {
        try {
            conn=JDBCUtils.getConnection();
            String sql="update books set name=?,price=?,number=?,press=? where id=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,b.getName());
            pstmt.setDouble(2,b.getPrice());
            pstmt.setInt(3,b.getNumber());
            pstmt.setString(4,b.getPress());
            pstmt.setInt(5,b.getId());
            int i = pstmt.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt,conn);
        }
    }

    @Override
    public void delete(int id) {
        Books books = select(id);
        if(books==null){
            System.out.println("您要删除的图书不存在！");
        }else{
            try {
                conn=JDBCUtils.getConnection();
                String sql="delete from books where id=?";
                pstmt=conn.prepareStatement(sql);
                pstmt.setInt(1,id);
                int i = pstmt.executeUpdate();
                if(i>0){
                    System.out.println("删除成功");
                }else{
                    System.out.println("删除失败！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
